﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;
using System.Collections.Generic;

namespace DataAccess
{
    public class PassengerDAL : BaseObject
    {
        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>

        private Passenger GetObjectFromReader(IDataReader reader)
        {
            Passenger temp = new Passenger();
            temp.PassengerID = (reader["PassengerID"] is DBNull) ? string.Empty : (System.String)reader["PassengerID"];
            temp.Password = (reader["Password"] is DBNull) ? string.Empty : (System.String)reader["Password"];
            temp.Name = (reader["Name"] is DBNull) ? string.Empty : (System.String)reader["Name"];
            temp.Age = (reader["Age"] is DBNull) ? int.MinValue : (System.Int32)reader["Age"];
            temp.Gender = (reader["Gender"] is DBNull) ? false : (System.Boolean)reader["Gender"];
            temp.CreditNumber = (reader["CreditNumber"] is DBNull) ? string.Empty : (System.String)reader["CreditNumber"];
            temp.Email = (reader["Email"] is DBNull) ? string.Empty : (System.String)reader["Email"];
            temp.Address = (reader["Address"] is DBNull) ? string.Empty : (System.String)reader["Address"];
            return temp;
        }

        ///<summary>
        ///Get List Passenger and store in a list 
        ///</summary>
        public Passenger Get_Passenger_By_ID(string ID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbPassengersByID, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PassengerID", ID));
                conn.Open();
                Passenger customer = new Passenger();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        customer = GetObjectFromReader(reader);
                    }
                }
                conn.Close();
                return customer;
            }
        }

        ///<summary>
        ///Get List Passenger and store in a list
        ///</summary>
        /// <param name="whereSql">Where fields. example : 1=1 </param>
        /// <param name="orderBy"Order fields></param>
        public List<Passenger> GetList_Passenger(string whereSql, string orderBy)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbPassengersDynamic, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@WhereCondition", whereSql));
                cmd.Parameters.Add(new SqlParameter("@OrderByExpression", orderBy));
                conn.Open();
                List<Passenger> lst = new List<Passenger>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        lst.Add(GetObjectFromReader(reader));
                    }
                }
                conn.Close();
                return lst;
            }
        }

        ///<summary>
        ///Get All Passenger and store in a list
        ///</summary>
        /// <param name="whereSql">Where fields. example : 1=1 </param>
        /// <param name="orderBy"Order fields></param>
        public List<Passenger> GetAll_Passenger()
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbPassengersAll, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                List<Passenger> lst = new List<Passenger>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        lst.Add(GetObjectFromReader(reader));
                    }
                }
                conn.Close();
                return lst;
            }
        }
        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
        public DataTable Get_PassengerAsPaging(string sWhere, string sOrderBy, int pageIndex, int pageSize)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelecttbPassengersPaged, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Where", sWhere));
                cmd.Parameters.Add(new SqlParameter("@OrderBy", sOrderBy));
                cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    DataTable tbl = new DataTable();
                    dataAdapter.Fill(tbl);
                    conn.Close();
                    return tbl;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Insert new Passenger
        ///</summary>
        public void Insert_Passenger(Passenger temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_InserttbPassenger, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PassengerID", temp.PassengerID));
                cmd.Parameters.Add(new SqlParameter("@Password", temp.Password));
                cmd.Parameters.Add(new SqlParameter("@Name", temp.Name));
                cmd.Parameters.Add(new SqlParameter("@Age", temp.Age));
                cmd.Parameters.Add(new SqlParameter("@Gender", temp.Gender));
                cmd.Parameters.Add(new SqlParameter("@CreditNumber", temp.CreditNumber));
                cmd.Parameters.Add(new SqlParameter("@Email", temp.Email));
                cmd.Parameters.Add(new SqlParameter("@Address", temp.Address));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Update a Passenger
        ///</summary>
        public void Update_Passenger(Passenger temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_UpdatetbPassenger, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PassengerID", temp.PassengerID));
                cmd.Parameters.Add(new SqlParameter("@Password", temp.Password));
                cmd.Parameters.Add(new SqlParameter("@Name", temp.Name));
                cmd.Parameters.Add(new SqlParameter("@Age", temp.Age));
                cmd.Parameters.Add(new SqlParameter("@Gender", temp.Gender));
                cmd.Parameters.Add(new SqlParameter("@CreditNumber", temp.CreditNumber));
                cmd.Parameters.Add(new SqlParameter("@Email", temp.Email));
                cmd.Parameters.Add(new SqlParameter("@Address", temp.Address));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Delete a Passenger
        ///</summary>
        public void Delete_Passenger(Passenger temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_DeletetbPassenger, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PassengerID", temp.PassengerID));
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        #endregion
    }
}
